#!/bin/bash
# 每半小时 执行
if [ -z "$1" ] ; then
    deal_date=$(date +%Y%m%d)
else
    deal_date=$1
fi


LOG_DIR="/home/cys/www/script/logs"
# 日志文件名包含日期，每次执行的日志前添加时间戳
FILE_NAME="update_aggregates_area_table"
LOG_FILE="${LOG_DIR}/${FILE_NAME}_$(date +%Y-%m-%d).log"
mkdir -p "${LOG_DIR}"

# 记录开始时间
echo "[$(date +%Y-%m-%d_%H:%M:%S)] 开始执行 clickhouse 命令" >> "${LOG_FILE}"
echo "处理日期：$deal_date" >> "${LOG_FILE}"


types=("cpa" "device")

# 遍历数组
for type in "${types[@]}"; do


  echo "=============处理t_data_user_${type}_aggregates_area表数据==============" >> "${LOG_FILE}"

  echo "=============1、 生成新分区数据到临时表==============" >> "${LOG_FILE}"

  clickhouse-client --password Keepgo123@cys -q "
    drop table IF EXISTS  db_cfx.t_temp_${type}_aggregates_area;
    "

  clickhouse-client --password Keepgo123@cys -q "
  CREATE TABLE db_cfx.t_temp_${type}_aggregates_area AS db_cfx.t_data_user_${type}_aggregates_area;
  "

  clickhouse-client --password Keepgo123@cys -q "
   INSERT INTO db_cfx.t_temp_${type}_aggregates_area
      SELECT
          activate_date as date,
          channel as channel,
          activate_area as activate_area,
          uniqCombinedState(oaid) as unique_oaids_state
      FROM db_cfx.t_data_user_${type}_active_final
      WHERE activate_date = '${deal_date}'
      GROUP BY date, channel, activate_area;
  "
  # 检查上一条命令是否执行成功
    if [ $? -eq 0 ]; then
      echo "t_temp_${type}_aggregates_area 临时表数据插入成功" >> "${LOG_FILE}"
    else
      echo "Error: t_temp_${type}_aggregates_area 的数据插入失败，quit"  >> "${LOG_FILE}"
      # 若需要失败即退出，取消下面一行的注释
      exit 1
    fi

  echo "=============2、 删除目标表${deal_date}分区==============" >> "${LOG_FILE}"
#  clickhouse-client --password Keepgo123@cys -q "
#    ALTER TABLE db_cfx.t_data_user_${type}_aggregates_area
#    DROP PARTITION ${deal_date};
#  "

  clickhouse-client --password Keepgo123@cys -q "
          ALTER TABLE db_cfx.t_data_user_${type}_aggregates_area
          delete where date= '${deal_date}';
        "
   if [ $? -eq 0 ]; then
        echo "db_cfx.t_data_user_${type}_aggregates_area表${deal_date}分区数据删除成功" >> "${LOG_FILE}"
      else
        echo "Error: db_cfx.t_data_user_${type}_aggregates_area表${deal_date}分区数据删除失败，quit"  >> "${LOG_FILE}"
        # 若需要失败即退出，取消下面一行的注释
        exit 1
  fi

  sleep 2

  echo "=============3、 把临时表的分区移动到目标表==============" >> "${LOG_FILE}"

#  clickhouse-client --password Keepgo123@cys -q "
#  ALTER TABLE db_cfx.t_data_user_${type}_aggregates_area
#  ATTACH PARTITION ${deal_date} FROM db_cfx.t_temp_${type}_aggregates_area
#
#  "
  clickhouse-client --password Keepgo123@cys -q "
    INSERT INTO db_cfx.t_data_user_${type}_aggregates_area
    SELECT *
    FROM db_cfx.t_temp_${type}_aggregates_area
    WHERE date = '${deal_date}';
  "
  if [ $? -eq 0 ]; then
          echo "临时表数据移动到正式表成功" >> "${LOG_FILE}"
        else
          echo "临时表数据移动到正式表失败，quit"  >> "${LOG_FILE}"
          # 若需要失败即退出，取消下面一行的注释
          exit 1
    fi

  echo "=============4、 删除临时表==============" >> "${LOG_FILE}"

  clickhouse-client --password Keepgo123@cys -q "
    DROP TABLE db_cfx.t_temp_${type}_aggregates_area
  "

  echo "=============处理t_data_user_${type}_aggregates_area表数据完成==============" >> "${LOG_FILE}"

done